package com.mr.pay;

import java.util.Map;

/**
 * SQL语句
 * @author liuxl
 *
 */
public class PaySQL {
	/**
	 * 查询客户和卡
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String custQuery(Map map) {
		StringBuffer sql = new StringBuffer();
		sql.append("select c.id_ id, b.name_ custname,b.cardno_ cardno,c.name_ cardname,");
		sql.append("b.phone_ phone,b.code_ code,l.balanceamount_ balance");
		sql.append(" from b_cust b,b_cust_line l,b_card c");
		sql.append(" where b.id_ = l.custid_");
		sql.append(" and l.cardid_ = c.id_");
		sql.append(" and b.enable_ = 1");
		sql.append(" and l.enable_ = 1");
		
		if( map != null ) {
			if( map.containsKey("l.cardtype_") ) {
				sql.append(" and l.cardtype_ = '").append(map.get("l.cardtype_")).append("'");
			}
			if( map.containsKey("b.phone_") ) {
				sql.append(" and b.phone_ = '").append(map.get("b.phone_")).append("'");
			}
		}
		return sql.toString();
	}
	
	/**
	 * 查询项目
	 * @param map
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String itemQuery(Map map) {
		StringBuffer sql = new StringBuffer();
		sql.append("select i.id_ itemId,");
		sql.append(" i.name_ itemName,");
		sql.append(" i.price_ price,");
		sql.append(" i.qcode_ qcode,");
		sql.append(" t.name_ typeName,");
		sql.append(" rootT.Name_ rootTypeName");
		sql.append(" from b_item i,b_item_type t,b_item_type rootT");
		sql.append(" where i.itemtypeid_ = t.id_");
		sql.append(" and t.rootid_ = rootT.Id_");  
		//and i.qcode_ = ?
		//and i.enable_ = 1
		if( map != null ) {
			if( map.containsKey("i.qcode_")) {
				sql.append(" and i.qcode_ like '").append(map.get("i.qcode_")).append("'||%");
			}
			if( map.containsKey("i.enable_")) {
				sql.append(" and i.enable_ = ").append(map.get("i.enable_"));
			}
			if( map.containsKey("idNotIn")) {
				sql.append(" and i.id_ not in (").append(map.get("idNotIn")).append(")");
			}
		}
		
		return sql.toString();
	}

}
